package dbi.org.api;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.HashMap;

import dbi.org.exception.ColumnDoesntExistsException;
import dbi.org.exception.DbDoesntExistsException;
import dbi.org.exception.TableDoesntExistsException;
import dbi.org.exception.TypeDoesntExistsException;
import dbi.org.model.Database;
import dbi.org.model.Table;
import dbi.org.model.Column;

public class DAO {
	private Connection connection = null;
	private static DAO dao = null;

	private String selectAllDatabases;
	private String selectTabellenByDatabase;
	private String selectVelden;
	private String selectVeldInfoFromGroupSleutel;
	private String selectAllTabellen;
	private String selectReferenties;
	private String selectConstraints;
	private String selectConstraintsByName;
	private String selectGroupsleutel;

	private PreparedStatement psAllDatabases;
	private PreparedStatement psTabellenByDatabase;
	private PreparedStatement psGetVelden;
	private PreparedStatement psGetConstraints;
	private PreparedStatement psGetGroepSleutels;
	private PreparedStatement psGetReferenties;
	private PreparedStatement psVeldInfoFromGroupSleutel;
	private PreparedStatement psAllTables;
	private PreparedStatement psConsByName;

	private ResultSet rs = null;

	private RDBMS rdbms = null;
	
	public static DAO getInstance() {
		if (dao == null)
			dao = new DAO();
		return dao;
	}
	public void clear() {
		dao = null;
	}

	private DAO() {
		try {
			ConnectionManager cm = ConnectionManager.getInstance();
			connection = cm.getConnection();

			rdbms = RDBMS.getInstance();

			selectAllDatabases = "Select * from `Database`";
			selectTabellenByDatabase = "Select * from `Table` Where `Database` = ?";
			selectVelden = "Select * from `Column` where `Database` = ? AND `Table` = ?";
			selectVeldInfoFromGroupSleutel = "Select * from `Column` where `Name` = ? AND `Database` = ? AND `Table` = ?";
			selectAllTabellen = "Select * from `Table`";
			selectReferenties = "Select * from `Reference` where `Name` = ?";
			selectConstraints = "Select * from `Constraint`";
			selectConstraintsByName = "Select * from `Constraint` where `Name` = ?";
			selectGroupsleutel = "Select * from `GroupKey` where `Name` = ?";
			
			psAllDatabases = connection.prepareStatement(selectAllDatabases);
			psTabellenByDatabase = connection
					.prepareStatement(selectTabellenByDatabase);
			psGetVelden = connection.prepareStatement(selectVelden);
			psGetConstraints = connection.prepareStatement(selectConstraints);
			psGetGroepSleutels = connection
					.prepareStatement(selectGroupsleutel);
			psGetReferenties = connection.prepareStatement(selectReferenties);
			psVeldInfoFromGroupSleutel = connection
					.prepareStatement(selectVeldInfoFromGroupSleutel);
			psAllTables = connection.prepareStatement(selectAllTabellen);
			psConsByName = connection.prepareStatement(selectConstraintsByName);

			// Databases
			rs = psAllDatabases.executeQuery();
			while (rs.next()) {
				rdbms.createDatabase(rs.getString(1));
			}
			// Tabellen
			for (Database db : rdbms.getDatabases().values()) {
				psTabellenByDatabase.setString(1, db.getName());
				rs = psTabellenByDatabase.executeQuery();
				while (rs.next()) {
					rdbms.createTable(rs.getString(3), rs.getString(1),
							rs.getString(2));
				}
			}
			// Velden
			for (Database database : rdbms.getDatabases().values()) {
				for (Table tbl : database.getTables().values()) {
					psGetVelden.setString(1, database.getName());
					psGetVelden.setString(2, tbl.getTableName());
					
					rs = psGetVelden.executeQuery();
					while (rs.next()) {
						rdbms.createColumn(rs.getString(1), rs.getString(3),
								rs.getString(2), rs.getString(4),
								rs.getBoolean(5));
					}

				}
			}
			// Constraints
			rs = psGetConstraints.executeQuery();
			ResultSet attribGroupSleutel = null;
			while (rs.next()) {
				psGetGroepSleutels.setString(1, rs.getString(3));
				attribGroupSleutel = psGetGroepSleutels.executeQuery();
				HashMap<String, Column> columns = new HashMap<String, Column>();
				
				while (attribGroupSleutel.next()) {
					Column column = getColumn(attribGroupSleutel.getString(2),
							attribGroupSleutel.getString(4),
							attribGroupSleutel.getString(3));
					columns.put(column.getName(), column);
				}

				rdbms.createConstraint(rs.getString(1), rs.getString(2),
						rs.getString(3), columns);
			}			
			//Ophalen van referenties
			ResultSet tblset = null;
			tblset = psAllTables.executeQuery();
			while (tblset.next()) {				
				ResultSet myResultSet = null;
				psConsByName.setString(1, tblset.getString(3));
				rs = psConsByName.executeQuery();
				while (rs.next()) {
					//System.out.println("ConsbyName: " +rs.getString(2) + rs.getString(3));
					if (rs.getString(2).startsWith("REF")) {
						psGetReferenties.setString(1, rs.getString(3));
						myResultSet = psGetReferenties.executeQuery();
						//System.out.println("Execute query " + psGetReferenties.toString());
						while (myResultSet.next()) {
							//System.out.println("Create Reference: " + myResultSet.getString(1)+ " " + myResultSet.getString(2)+ " "  + myResultSet.getString(3));
							rdbms.createReference(myResultSet.getString(1), myResultSet.getString(2), myResultSet.getString(3));
						}

					}
				}
			}

		} catch (Exception e) {
			e.printStackTrace();
		}

		

	}

	private Column getColumn(String columnName, String dbName, String tableName) throws TypeDoesntExistsException, ColumnDoesntExistsException, TableDoesntExistsException, DbDoesntExistsException {
		//Het ophalen van de desbetreffende velden uit de RDMS
		Column column = null;
		if(rdbms.getDatabases().containsKey(dbName)) {
			if(rdbms.getDatabases().get(dbName).getTables().containsKey(tableName)) {
				if(rdbms.getDatabases().get(dbName).getTables().get(tableName).getColumns().containsKey(columnName)) {
					column =  rdbms.getDatabases().get(dbName).getTables().get(tableName).getColumns().get(columnName);
				} else {
					throw new ColumnDoesntExistsException("Column bestaat niet");
				}				
			} else {
				throw new TableDoesntExistsException();
			}
		} else {
			throw new DbDoesntExistsException();
		}		
		return column;
	}

}
